package dbservlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import bean.Page;
import bean.StudentInfo;
 

public class AllServlet extends HttpServlet{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private ArrayList<Connection> conns;
	private ArrayList<HashMap<String,String>> databasesInfo;
	private ArrayList<ArrayList<HashMap<String,String>>> databasesInfos;
	
	public AllServlet() throws ServletException{
		try {
			conns = new ArrayList<Connection>();
			databasesInfo = Utils.praseDatabasesXML("D:\\JavaEEWS\\MVC-test\\databases\\databases.xml");
			databasesInfos = new ArrayList<ArrayList<HashMap<String,String>>>();
			for(int i = 0; i < databasesInfo.size(); i++) {
				Connection conn=null; 
			    Class.forName("com.mysql.jdbc.Driver");
			    String url = "jdbc:mysql://"
			    		+databasesInfo.get(i).get("connectUrl")
			    		+":"
			    		+databasesInfo.get(i).get("port")
			    		+"/"
			    		+databasesInfo.get(i).get("databaseName")
			    		+"?user="
			    		+databasesInfo.get(i).get("user")
			    		+"&password="
			    		+databasesInfo.get(i).get("password")
			    		+"&useUnicode=true&characterEncoding=UTF8";
			    System.out.println(url);
			    try {
					conn=DriverManager.getConnection(url);
					conns.add(conn);
				} catch (SQLException e) {
					System.out.println("与第" + i + "个数据库连接出错:" + e.getMessage());
					e.printStackTrace();
					System.exit(0);
				}
			    databasesInfos.add(Utils.praseDatabasesXML("D:\\JavaEEWS\\MVC-test\\databases\\" + databasesInfo.get(i).get("dataStructionDescribeFile")));
			}
		} catch (IOException e1) {
			System.out.println("读取文件发生错误:" + e1.getMessage());
			e1.printStackTrace();
			System.exit(0);
		} catch (ClassNotFoundException e2) {
			System.out.println("jdbc驱动出现问题:" + e2.getMessage());
			e2.printStackTrace();
			System.exit(0);
		}
	}
 
	 //doPost方法 不用做修改
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		    request.setCharacterEncoding("UTF-8");
   		    response.setCharacterEncoding("UTF-8");
			String methodName=request.getParameter("methodName");
			int method=Integer.parseInt(methodName);
		try {  
			switch(method)
		       {
		    	case 0:
					insert(request,response);
		        case 1:
                    difpage(request,response);
			        break;    
		    	case 2:
  				    delete(request,response);
  			        break;       
		        case 3:
  				    update(request,response);
  				    break;
		        case 4:
		        	update1(request,response);
			        break;
		        case 5:
		        	dispatch(request,response);
			        break;
		       }
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
	}
	//doGet方法 不用做修改
	public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
       doPost(request,response);
    }
	
	//关闭数据库资源 不用做修改
	public void close(Statement stat,Connection conn) throws SQLException{
		if(stat!=null){
	    	   stat.close();
	    }
	    if(conn!=null){
	    	   conn.close();
	    }
	}
	//插入方法 修改完成
	public void insert(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException{
    	Connection conn=null;
    	Statement stat=null;
		String id=request.getParameter("id");
        String name=request.getParameter("name");
        String age=request.getParameter("age");
        String gender=request.getParameter("gender");
        String major=request.getParameter("major");
        String school=request.getParameter("school");
        boolean flag = true;
        for(int i = 0; i < databasesInfo.size(); i++) {
        	if(school.equals(databasesInfo.get(i).get("schoolName"))) {//根据学校名找到对应数据库，这个所以这个for只会执行一遍主要代码
        		//执行插入命令
        		ArrayList<HashMap<String,String>> temp = databasesInfos.get(i);
        		stat = conns.get(i).createStatement();
        		String sql = "insert into "
                		+databasesInfo.get(i).get("studentIfnoTableName")
                		+"("
                		+Utils.getColName(temp, "id")
                		+","
                		+Utils.getColName(temp, "name")
                		+","
                		+Utils.getColName(temp, "age")
                		+","
                		+Utils.getColName(temp, "gender")
                		+","
                		+Utils.getColName(temp, "major")
                		+") values("
                		+Utils.getTypedVar(temp, "id", id)
                		+","
                		+Utils.getTypedVar(temp, "name", name)
                		+","
                		+Utils.getTypedVar(temp, "age", age)
                		+","
                		+Utils.getTypedVar(temp, "gender", gender)
                		+","
                		+Utils.getTypedVar(temp, "major", major)
                		+");";
        		System.out.println(sql);
        		stat.execute(sql); 
            	close(stat,conn);
            	flag = false;
            	break;
        	}
        }
        if(flag) {
        	System.out.println("没有找到这个学校，插入失败，系统未作任何处理。");
        }
        
    }
    //查询方法 修改完成
    public ArrayList<StudentInfo> select(String school, String id, String name) throws ClassNotFoundException, SQLException{
    	Connection conn=null;
    	Statement stat=null;
	    ResultSet rs=null;
	    ArrayList<StudentInfo> result = new ArrayList<StudentInfo>();
	    
	    if(school.equals("")) {//学校未指定
	    	for(int i = 0; i < databasesInfo.size(); i++) {
	    		stat=conns.get(i).createStatement();
        		String tableName = databasesInfo.get(i).get("studentIfnoTableName");
            	if(id==""&&name==""){
            		System.out.println("select * from " + tableName);
            	    rs=stat.executeQuery("select * from " + tableName); 
            	}
            	if(id!=""&&name==""){
            		String sql = "select * from " 
           	        		+ tableName 
           	        		+ " where " 
           	        		+ Utils.getColName(databasesInfos.get(i), "id") 
           	        		+ "="
           	        		+ Utils.getTypedVar(databasesInfos.get(i), "id", id);
            		System.out.println(sql);
           	        rs=stat.executeQuery(sql); 
             	}
            	if(id==""&&name!=""){
            		String sql = "select * from " 
           	        		+ tableName 
           	        		+ " where "
           	        		+ Utils.getColName(databasesInfos.get(i), "name")
           	        		+ "="
           	        		+ Utils.getTypedVar(databasesInfos.get(i), "name", name);
            		System.out.println(sql);
           	        rs=stat.executeQuery(sql); 
           	    }
            	if(id!=""&&name!=""){
            		String sql = "select * from " 
              	    		+ tableName 
              	    		+ " where "
              	    		+ Utils.getColName(databasesInfos.get(i), "id")
              	    		+ "="
              	    		+ Utils.getTypedVar(databasesInfos.get(i), "id", id)
              	    		+ " and "
              	    		+ Utils.getColName(databasesInfos.get(i), "name")
              	    		+ "="
              	    		+ Utils.getTypedVar(databasesInfos.get(i), "name", name);
            		System.out.println(sql);
              	    rs=stat.executeQuery(sql); 
              	}
            	while(rs.next())
                {
                	StudentInfo st=new StudentInfo();
                	st.setId(rs.getString(Utils.getColName(databasesInfos.get(i), "id")));
                	st.setName(rs.getString(Utils.getColName(databasesInfos.get(i), "name")));
                	st.setAge(rs.getString(Utils.getColName(databasesInfos.get(i), "age")));
                	st.setGender(rs.getString(Utils.getColName(databasesInfos.get(i), "gender")));
                	st.setMajor(rs.getString(Utils.getColName(databasesInfos.get(i), "major")));
                	st.setSchool(databasesInfo.get(i).get("schoolName"));
                	result.add(st);	
                }
	        }
	    }else {//学校指定
	    	boolean flag = true;
	    	for(int i = 0; i < databasesInfo.size(); i++) {
	        	if(school.equals(databasesInfo.get(i).get("schoolName"))) {//根据学校名找到对应数据库，这个所以这个for只会执行一遍主要代码
	        		stat=conns.get(i).createStatement();
	        		String tableName = databasesInfo.get(i).get("studentIfnoTableName");
	            	if(id==""&&name==""){
	            		System.out.println("select * from " + tableName);
	            	    rs=stat.executeQuery("select * from " + tableName); 
	            	}
	            	if(id!=""&&name==""){
	            		String sql = "select * from " 
	           	        		+ tableName 
	           	        		+ " where " 
	           	        		+ Utils.getColName(databasesInfos.get(i), "id") 
	           	        		+ "="
	           	        		+ Utils.getTypedVar(databasesInfos.get(i), "id", id);
	            		System.out.println(sql);
	           	        rs=stat.executeQuery(sql); 
	             	}
	            	if(id==""&&name!=""){
	            		String sql = "select * from " 
	           	        		+ tableName 
	           	        		+ " where "
	           	        		+ Utils.getColName(databasesInfos.get(i), "name")
	           	        		+ "="
	           	        		+ Utils.getTypedVar(databasesInfos.get(i), "name", name);
	            		System.out.println(sql);
	           	        rs=stat.executeQuery(sql); 
	           	    }
	            	if(id!=""&&name!=""){
	            		String sql = "select * from " 
	              	    		+ tableName 
	              	    		+ " where "
	              	    		+ Utils.getColName(databasesInfos.get(i), "id")
	              	    		+ "="
	              	    		+ Utils.getTypedVar(databasesInfos.get(i), "id", id)
	              	    		+ " and "
	              	    		+ Utils.getColName(databasesInfos.get(i), "name")
	              	    		+ "="
	              	    		+ Utils.getTypedVar(databasesInfos.get(i), "name", name);
	            		System.out.println(sql);
	              	    rs=stat.executeQuery(sql); 
	              	}
	            	while(rs.next())
	                {
	                	StudentInfo st=new StudentInfo();
	                	st.setId(rs.getString(Utils.getColName(databasesInfos.get(i), "id")));
	                	st.setName(rs.getString(Utils.getColName(databasesInfos.get(i), "name")));
	                	st.setAge(rs.getString(Utils.getColName(databasesInfos.get(i), "age")));
	                	st.setGender(rs.getString(Utils.getColName(databasesInfos.get(i), "gender")));
	                	st.setMajor(rs.getString(Utils.getColName(databasesInfos.get(i), "major")));
	                	st.setSchool(databasesInfo.get(i).get("schoolName"));
	                	result.add(st);	
	                }
	            	flag = false;
	            	break;
	        	}
	        }
	    	if(flag) {
	        	System.out.println("没有找到这个学校，select失败，系统未作任何处理。");
	        }
	    }
        if(rs!=null){
	    	rs.close();
	    }
	    close(stat,conn);
    	return result;
    }
    //条件查询跳转 修改完成
    public void dispatch(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException{
    	String id5=request.getParameter("id");
    	String name5=request.getParameter("name");
    	String school=request.getParameter("school");
        if(select(school,id5,name5).isEmpty()){
        	request.getRequestDispatcher("selectnothing.jsp").forward(request, response);
        }
        else{
    		request.setAttribute("result", select(school,id5,name5));
            request.getRequestDispatcher("idnameselect.jsp").forward(request, response);	
        }
    }
    //设置分页相关参数方法
	public Page setpage(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException{
		String crd=request.getParameter("currentRecord");
    	ArrayList<StudentInfo> result = new ArrayList<StudentInfo>();
    	for(int i = 0; i < databasesInfo.size(); i++) {
    		result.addAll(select(databasesInfo.get(i).get("schoolName"),"",""));
    	}
    	Page pager=new Page();
    	pager.setTotalRecord(result.size()); 
    	pager.setTotalPage(result.size(),pager.getPageSize());
    	if(crd!=null)
        {
    		int currentRecord=Integer.parseInt(crd);
            pager.setCurrentRecord(currentRecord);
            pager.setCurrentPage(currentRecord,pager.getPageSize());
        }
    	return pager;
	}
	//获得分页显示的子集
	 public void difpage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException{
	     ArrayList<StudentInfo> result = new ArrayList<StudentInfo>();
	     for(int i = 0; i < databasesInfo.size(); i++) {
	    	 result.addAll(select(databasesInfo.get(i).get("schoolName"),"",""));
	     }
		 Page pager=new Page();
		 pager=setpage(request,response);
  	     List<StudentInfo> subResult=null;
  	     int currentRecord=pager.getCurrentRecord();
         if(currentRecord==0){
         	if(pager.getTotalRecord()<8){
         		subResult=(List<StudentInfo>) result.subList(0,pager.getTotalRecord());
         	}
         	else{
         		subResult=(List<StudentInfo>) result.subList(0,pager.getPageSize());
         	}         
         }
         else if(pager.getCurrentRecord()+pager.getPageSize()<result.size())
         {
               subResult=(List<StudentInfo>) result.subList(pager.getCurrentRecord(),pager.getCurrentRecord()+pager.getPageSize());
         }
         else
         {
              subResult=(List<StudentInfo>) result.subList(pager.getCurrentRecord(),result.size());
         }
         request.setAttribute("pager", pager);
	     request.setAttribute("subResult", subResult);
		 request.getRequestDispatcher("layout.jsp").forward(request, response);
     }
    //信息删除方法
    public void delete(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException{
 		String id2=request.getParameter("id");
 		String school=request.getParameter("school");
        for(int i = 0; i < databasesInfo.size(); i++) {
        	if(school.equals(databasesInfo.get(i).get("schoolName"))) {//根据学校名找到对应数据库，这个所以这个for只会执行一遍主要代码
        		Statement stat = conns.get(i).createStatement();
        		String sql = "delete from "
        				+ databasesInfo.get(i).get("studentIfnoTableName")
        				+ " where "
        				+ Utils.getColName(databasesInfos.get(i), "id")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "id", id2);
        		System.out.println(sql);
        		stat.execute(sql);
        		request.getRequestDispatcher("delete.jsp").forward(request, response);
        		break;
        	}
        }
    } 
    //信息修改方法
    public void update1(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException{
    	String id4=request.getParameter("id");
    	String school=request.getParameter("school");
	    request.setAttribute("result", select(school,id4,""));
        request.getRequestDispatcher("update1.jsp").forward(request, response);
    }   
    public void update(HttpServletRequest request, HttpServletResponse response) throws ClassNotFoundException, SQLException, ServletException, IOException{
        String id3=request.getParameter("id");
        String name3=request.getParameter("name");
        String age3=request.getParameter("age");
        String gender3=request.getParameter("gender");
        String major3=request.getParameter("major");
        String school=request.getParameter("school");
        for(int i = 0; i < databasesInfo.size(); i++) {
        	if(school.equals(databasesInfo.get(i).get("schoolName"))) {//根据学校名找到对应数据库，这个所以这个for只会执行一遍主要代码
        		Statement stat = conns.get(i).createStatement();
        		String sql = "update "
        				+ databasesInfo.get(i).get("studentIfnoTableName")
        				+ " set "
        				+ Utils.getColName(databasesInfos.get(i), "id")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "id", id3)
        				+ ","
        				+ Utils.getColName(databasesInfos.get(i), "name")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "name", name3)
        				+ ","
        				+ Utils.getColName(databasesInfos.get(i), "age")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "age", age3)
        				+ ","
        				+ Utils.getColName(databasesInfos.get(i), "gender")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "gender", gender3)
        				+ ","
        				+ Utils.getColName(databasesInfos.get(i), "major")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "major", major3)
        				+ " where "
        				+ Utils.getColName(databasesInfos.get(i), "id")
        				+ "="
        				+ Utils.getTypedVar(databasesInfos.get(i), "id", id3);
        		System.out.println(sql);
        		stat.execute(sql);
        		request.setAttribute("result", select(school,id3,""));    
        	    request.getRequestDispatcher("update.jsp").forward(request, response);
        	    break;
        	}
        }
    } 
 
}
